

//
//  DataBaseHandle.m
//  电影豆瓣--完整版
//
//  Created by dllo on 16/1/8.
//  Copyright © 2016年 lanou. All rights reserved.
//

#import "DataBaseHandle.h"
#import "MyModel.h"
#import "UserModel.h"

@implementation DataBaseHandle


+(DataBaseHandle *)sharedDataBaseHandle {
    
    static DataBaseHandle *handle = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        handle = [[DataBaseHandle alloc] init];
    });
    return handle;
}


- (void)openDB {
    
    NSString *document = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    NSString *dbPath = [document stringByAppendingPathComponent:@"豆瓣.sqlite"];
    NSLog(@"%@", dbPath);
    
    int result = sqlite3_open(dbPath.UTF8String, &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"打开失败");
    }
    
    
}


- (void)createTable {
    
    NSString *sqlStr = [NSString stringWithFormat:@"create table if not exists Activity(number integer primary  key autoincrement, userName text, title text)"];
    NSString *sqlStr2 = [NSString stringWithFormat:@"create table if not exists Movie(number integer primary  key autoincrement, userName text, title text)"];
    NSString *sqlStr4 = [NSString stringWithFormat:@"create table if not exists User(userID integer primary key autoincrement, userName text, password text, email text, telephone text, state text)"];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    } else {
        NSLog(@"建表失败");
    }
    int result2 = sqlite3_exec(dbPoint, sqlStr2.UTF8String, NULL, NULL, NULL);
    if (result2 == SQLITE_OK) {
        NSLog(@"建表成功");
    } else {
        NSLog(@"建表失败");
    }
    int result4 = sqlite3_exec(dbPoint, sqlStr4.UTF8String, NULL, NULL, NULL);
    if (result4 == SQLITE_OK) {
        NSLog(@"建表成功");
    } else {
        NSLog(@"建表失败");
    }


}

#pragma mark - 电影和活动的增删改查的方法
- (void)insertModel:(MyModel *)model table:(NSString *)tableName{
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into %@(userName ,title) values('%@', '%@')", tableName, model.userName, model.title];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    } else {
        NSLog(@"添加失败");
    }
    
}

- (void)deleteModel:(MyModel *)model table:(NSString *)tableName{
    
    NSString *sqlStr = [NSString stringWithFormat:@"delete from %@ where title = '%@'", tableName, model.title];
    
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
}

// 按标题查询
- (NSMutableArray *)selectModel:(MyModel *)model table:(NSString *)tableName{
    
    NSString *sqlStr = [NSString stringWithFormat:@"select *from %@ where userName = '%@' and title = '%@' ", tableName, model.userName,model.title];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            MyModel *model = [MyModel myModel];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            [array addObject:model];
        }
    }
    return array;
}

// 查询表里的全部内容
- (NSMutableArray *)selectAllFromtable:(NSString *)tableName{
    UserModel *user = [[DataBaseHandle sharedDataBaseHandle] selectUserState:@"是"];
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from '%@' where userName = '%@'", tableName, user.userName];
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            MyModel *model = [MyModel myModel];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            [array addObject:model];
        }
    }
    return array;

}


#pragma mark - 用户信息的增删改查方法

- (void)insertUserModel:(UserModel *)model {
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into User(userName, password, email, telephone, state) values('%@', '%@', '%@', '%@', '%@')", model.userName, model.password, model.email, model.telepnone, model.state];
    int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
    
}

- (UserModel *)selectUser:(NSString *)userName {
    NSString *sqlStr = [NSString stringWithFormat:@"select *from User where userName = '%@' ", userName];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    UserModel *model = [UserModel userModel];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *password = sqlite3_column_text(stmt, 2);
            const unsigned char *email = sqlite3_column_text(stmt, 3);
            const unsigned char *phone = sqlite3_column_text(stmt, 4);
            const unsigned char *state = sqlite3_column_text(stmt, 5);
            model.userName = [NSString stringWithUTF8String:(const char *)name];
            model.password = [NSString stringWithUTF8String:(const char *)password];
            model.email = [NSString stringWithUTF8String:(const char *) email];
            model.telepnone = [NSString stringWithUTF8String:(const char *) phone];
            model.state = [NSString stringWithUTF8String:(const char *)state];
        }
    }
    return model;
    
}


- (UserModel *)selectUserState:(NSString *)userState {
    NSString *sqlStr = [NSString stringWithFormat:@"select *from User where state = '%@' ", userState];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dbPoint, sqlStr.UTF8String, -1, &stmt, NULL);
    UserModel *model = [UserModel userModel];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *password = sqlite3_column_text(stmt, 2);
            const unsigned char *email = sqlite3_column_text(stmt, 3);
            const unsigned char *phone = sqlite3_column_text(stmt, 4);
            const unsigned char *state = sqlite3_column_text(stmt, 5);
            model.userName = [NSString stringWithUTF8String:(const char *)name];
            model.password = [NSString stringWithUTF8String:(const char *)password];
            model.email = [NSString stringWithUTF8String:(const char *) email];
            model.telepnone = [NSString stringWithUTF8String:(const char *) phone];
            model.state = [NSString stringWithUTF8String:(const char *)state];
        }
    }
    return model;

}



- (void)updateTable:(UserModel *)model {
    
    if ([model.state isEqualToString:@"是"]) {
        NSString *sqlStr = [NSString stringWithFormat:@"update User set state = '否' where userName = '%@' ", model.userName];
        int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
        if (result == SQLITE_OK) {
            NSLog(@"修改成功");
        } else {
            NSLog(@"修改失败");
        }
    } else if ([model.state isEqualToString:@"否"]) {
        NSString *sqlStr = [NSString stringWithFormat:@"update User set state = '是' where userName = '%@' ", model.userName];
        int result = sqlite3_exec(dbPoint, sqlStr.UTF8String, NULL, NULL, NULL);
        if (result == SQLITE_OK) {
            NSLog(@"修改成功");
        } else {
            NSLog(@"修改失败");
        }
    }
    
}

@end
